# Alex Gazmararian
# agazmararian@gmail.com

library(tidyverse)
library(tidylog)
library(readxl)
library(here)
library(janitor)
library(modelsummary)

emp_files <- list.files(here("data", "input", "bls_unemployment"), pattern = "\\.xlsx$", full.names = TRUE)
emp <- map_dfr(emp_files, function(x) {
  emp <- suppressWarnings(read_xlsx(x, skip = 1, na = c("", "NA", "NANA"), progress = FALSE))
  emp <- clean_names(emp)
})
names(emp) <- c("laus", "fipst", "fipscty", "county", "year", "force", "emp", "unemp", "urate")
emp <- emp %>%
  filter(!is.na(fipst))
emp$fips <- paste0(str_pad(emp$fipst, 2, "left", "0"), str_pad(emp$fipscty, 3, "left", "0"))
emp$fips <- as.numeric(emp$fips)

emp <- filter(emp, !is.na(urate))
emp$year <- as.numeric(emp$year)

# Lag one year
emp$year_lag <- emp$year + 1

# Harmonize FIPS
emp$fips.post <- emp$fips
emp$fips[emp$fips == 09120] <- 09001

# Get relevant columns
emp <- subset(emp, select = c(fips.post, year:year_lag))

emp$force_ln <- log(emp$force)

write_csv(emp, here("data", "inter", "emp_county_processed.csv"))
message("[OK] Saved unemployment data")